/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package logical_spaces.business;

import Entity.TableSpaceInfo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import logical_spaces.DAO.GraphicalLogicalSpaceDAO;

/**
 *
 * @author juan
 */
public class GraphicsLogicalSpaceBusiness {
 
    private ArrayList<TableSpaceInfo> tablespaceInfos;
    public Connection conn;   

    public GraphicsLogicalSpaceBusiness(Connection conn) {
        this.conn = conn;
    }
    
    public ArrayList<TableSpaceInfo> info() {
       this.tablespaceInfos = new ArrayList<>();
       
        
       String sql= "SELECT 1 seq, b.tablespace_name, nvl(x.fs,0)/y.ap*100 pf, b.file_name file_name,\n"
                + "b.bytes/1 a_byt, NVL((b.bytes-SUM(f.bytes))/1,b.bytes/1) t_byt,\n"
                + "NVL(SUM(f.bytes)/1,0) f_byt, NVL(SUM(f.bytes)/b.bytes*100,0) pct_free\n"
                + "FROM dba_free_space f, dba_data_files b\n"
                + ",(SELECT y.tablespace_name, SUM(y.bytes) fs\n"
                + "FROM dba_free_space y GROUP BY y.tablespace_name) x\n"
                + ",(SELECT x.tablespace_name, SUM(x.bytes) ap\n"
                + "FROM dba_data_files x GROUP BY x.tablespace_name) y\n"
                + "WHERE f.file_id(+) = b.file_id\n"
                + "AND x.tablespace_name(+) = y.tablespace_name\n"
                + "AND y.tablespace_name = b.tablespace_name\n"
                + "AND f.tablespace_name(+) = b.tablespace_name\n"
                + "GROUP BY b.tablespace_name, nvl(x.fs,0)/y.ap*100, b.file_name, b.bytes\n"
                + "UNION\n"
                + "SELECT 2 seq, tablespace_name,\n"
                + "j.bf/k.bb*100 pf, b.name file_name, b.bytes/1 a_byt,\n"
                + "a.bytes_used/1 t_byt, a.bytes_free/1 f_byt,\n"
                + "a.bytes_free/b.bytes*100 pct_free\n"
                + "FROM v$temp_space_header a, v$tempfile b\n"
                + ",(SELECT SUM(bytes_free) bf FROM v$temp_space_header) j\n"
                + ",(SELECT SUM(bytes) bb FROM v$tempfile) k\n"
                + "WHERE a.file_id = b.file#\n"
                + "ORDER BY 1,2,4,3"; 
       
       try{
            ResultSet rs = new GraphicalLogicalSpaceDAO(conn).executeStatement( sql );
            
            while( rs.next() ){
                ArrayList<String> stringList = new ArrayList<>();
                for(int i = 1 ; i <= rs.getMetaData().getColumnCount() ; i++){
                    stringList.add( rs.getString(i) );
                    
                }
                
                Double a = 0.0;
                Double b = 0.0;
                Double totalMb = 0.0;
                Double freeMb = 0.0;
                Double usageMb = 0.0;
                if(stringList.get(2)!=null)
                    a = redondear(Double.valueOf(stringList.get(2)).doubleValue(),2);
                if(stringList.get(2)!=null)
                    b = redondear(100-Double.valueOf(stringList.get(2)).doubleValue(),2);
                if(stringList.get(4)!=null)
                    totalMb = redondear(Double.valueOf(stringList.get(4)).doubleValue()/1048576,2);
                if(stringList.get(6)!=null)
                    freeMb = redondear(Double.valueOf(stringList.get(6)).doubleValue()/1048576,2);
                if(stringList.get(5)!=null)
                    usageMb = redondear(Double.valueOf(stringList.get(5)).doubleValue()/1048576,2);
                
                tablespaceInfos.add( new TableSpaceInfo( stringList.get(1),
                        b, a, totalMb, freeMb, usageMb ));
                
            }
        } catch ( SQLException | NumberFormatException e ){
             FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                    "Error de Consulta!",
                    "Please Try Again! " + e.getMessage()));
        }
        
        return tablespaceInfos;
                
    }
    
    
    public double redondear(double numero,int digitos)
    {
        int cifras=(int) Math.pow(10,digitos);
        return Math.rint(numero*cifras)/cifras;
    }

}
